/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.services;

/**
 *
 * @author jay
 */

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.model.SelectItem;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.primefaces.model.SortOrder;
import src.HibernateUtil;
import src.domain.system.Numbering;
import src.util.Utils;

public class CRUEDService implements java.io.Serializable {
    
    Session session;
        
    public CRUEDService() {
        
    }

    public List findAll(String tableName, Map<String, String> filters) {
        List result = new ArrayList();
        
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
              
        try {
            
            String where = "";
            String order = "";
            
            if (filters!=null) {
                
                for (Map.Entry<String, String> e : filters.entrySet())
                    if (e.getKey().toLowerCase().equals("order")) 
                        order += (order.length() > 0 ? "," : "") + " tb." + e.getValue();
                    
                    else if (e.getValue()==null)
                        where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NULL ";  
                    
                    else where += (where.length()>0 ? " AND " : "") + " lower(tb." + e.getKey() + ") like lower('%" + e.getValue() + "%')";
            }
            
            String sql = "from "+tableName+" as tb "
                    + (where.length() > 0 ? " where " +  where : "")
                    + (order.length() > 0 ? " order by " +  order : "");
                    
            Query q = session.createQuery (sql);
            result = (List) q.list();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
               
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);    
        }
        
        finally { }
        
        return result;
    }
    
    
    public List findAllSQL(String sql, Map<String, String> filters) {
        List result = new ArrayList();
        
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
              
        try {
            
            String where = "";
            if (filters!=null) {
                for (Map.Entry<String, String> e : filters.entrySet())
                    where += (where.length()>0 ? " AND " : "") + " lower(tb." + e.getKey() + ") like lower('%" + e.getValue() + "%')";
            }
            
            Query q = session.createQuery(sql+ (where.length() > 0 ? " where " +  where : ""));
            result = (List) q.list();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);               
        }
        
        finally { }
        
        return result;
    }
    
    
    //chan
    public List findAllSQLAdvance(String sql, Map<String, Object> filters,String orderby) {
        List result = new ArrayList();
        
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
              
        try {
            
            String where = "";
            
            if (filters!=null) {
                for (Map.Entry<String, Object> e : filters.entrySet())
                    if (e.getValue() instanceof String) {
                    
                        if (e.getValue()==null)
                            where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NULL ";  
                        else if(e.getValue().toString().toLowerCase().equals("notnull"))
                            where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NOT NULL ";   
                        else if(e.getValue().toString().toLowerCase().equals("false"))
                            where += (where.length()>0 ? " AND ((" : "") + e.getKey() + " = false) or ("+ e.getKey() + " is null))";
                        else
                            where += (where.length()>0 ? " AND " : "") + " lower(" + e.getKey() + ") like lower('%" + e.getValue() + "%')";
                    }
                    //tipe date..chan
                    else if (e.getValue() instanceof  Date){
                        where += (where.length()>0 ? " AND " : "") + e.getKey() + " = " + Utils.formatSqlDate((Date)e.getValue());      
                    }                    
                    //filter bersarang
                    else if (e.getValue() instanceof Map) {
                        Map<Object, Object> filter2 = (Map<Object, Object>) e.getValue();
                        for (Map.Entry<Object, Object> e2 : filter2.entrySet()) {
                            where += (where.length()>0 ? " AND " : "") + e.getKey() + e2.getKey() + e2.getValue();    
                        }
                    }
                    
                    else if (e.getValue() == null) {
                        where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NULL ";  
                    }
                    
                    else //jika bukan string pake samadengan aje
                        where += (where.length()>0 ? " AND " : "") + e.getKey() + " = " + e.getValue();      
            }
            
            String hql = sql+ (where.length() > 0 ? " where " +  where : "") + orderby;
            Query q = session.createQuery(hql);
            result = (List) q.list();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        
        return result;
    }
    
    public int countAllSQLAdvance(String sql, Map<String, Object> filters,String orderby) {
        Long reccount = Long.valueOf("1");
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
              
        try {
            
            String where = "";
            
            if (filters!=null) {
                for (Map.Entry<String, Object> e : filters.entrySet())
                    if (e.getValue() instanceof String) {
                    
                        if (e.getValue()==null)
                            where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NULL ";  
                        else if(e.getValue().toString().toLowerCase().equals("notnull"))
                            where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NOT NULL ";
                        else if(e.getValue().toString().toLowerCase().equals("false"))
                            where += (where.length()>0 ? " AND ((" : "") + e.getKey() + " = false) or ("+ e.getKey() + " is null))";
                        else
                            where += (where.length()>0 ? " AND " : "") + " lower(" + e.getKey() + ") like lower('%" + e.getValue() + "%')";
                        
                    }
                    
                    //filter bersarang
                    else if (e.getValue() instanceof Map) {
                        Map<Object, Object> filter2 = (Map<Object, Object>) e.getValue();
                        for (Map.Entry<Object, Object> e2 : filter2.entrySet()) {
                            where += (where.length()>0 ? " AND " : "") + e.getKey() + e2.getKey() + e2.getValue();    
                        }
                    }
                    
                    else if (e.getValue() == null) {
                        where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NULL ";  
                    }
                    
                    else //jika bukan string pake samadengan aje
                        where += (where.length()>0 ? " AND " : "") + e.getKey() + " = " + e.getValue();      
            }
            
            String hql = sql+ (where.length() > 0 ? " where " +  where : "") + orderby;
            Query q = session.createQuery(hql);
            reccount = (Long) q.uniqueResult();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        
        return reccount.intValue();
    }
    
    
    public List findSQLDirect(String sql, Map<String, Object> filters,String orderby) {
        List result = new ArrayList();
        
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
              
        try {
            
            String where = "";
            
            if (filters!=null) {
                for (Map.Entry<String, Object> e : filters.entrySet())
                    if (e.getValue() instanceof String) {
                    
                        if (e.getValue()==null)
                            where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NULL ";  
                        else
                            where += (where.length()>0 ? " AND " : "") + " lower(" + e.getKey() + ") like lower('%" + e.getValue() + "%')";
                    }
                    
                    //filter bersarang
                    else if (e.getValue() instanceof Map) {
                        Map<Object, Object> filter2 = (Map<Object, Object>) e.getValue();
                        for (Map.Entry<Object, Object> e2 : filter2.entrySet()) {
                            where += (where.length()>0 ? " AND " : "") + e.getKey() + e2.getKey() + e2.getValue();    
                        }
                    }
                    
                    else //jika bukan string pake samadengan aje
                        where += (where.length()>0 ? " AND " : "") + e.getKey() + " = " + e.getValue();    
            }
            
            String hql = sql+ (where.length() > 0 ? " where " +  where : "") + orderby;
            Query q = session.createSQLQuery(hql);
            result = (List) q.list();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        
        return result;
    }
    
    
    public List<SelectItem> listAll(String tableName, String value, String label) {
        List<SelectItem> result = new ArrayList<>();
        
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
                      
        try {
                        
            //Query q = session.createQuery ("select tb."+value+", tb."+label+" from "+tableName+" as tb order by tb."+label);
            Query q = session.createQuery ("select "+value+", "+label+" from "+tableName+" as tb order by "+label);
            List<Object[]> ls = (List) q.list();
            
            for (Object[] obj : ls) {
                result.add(new SelectItem(obj[0], (String) obj[1]));
            }
          
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                   
                } catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        
        return result;
    }
    
    //chan
    public List<SelectItem> listAll(String tableName, String value, String label, Map<String, String> filters) {
        List<SelectItem> result = new ArrayList<>();
        
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
                      
        try {
            String where = "";
            if (filters!=null) {
                for (Map.Entry<String, String> e : filters.entrySet())
                    //where += (where.length()>0 ? " AND " : "") + " lower(tb." + e.getKey() + ") like lower('%" + e.getValue() + "%')";
                    where += (where.length()>0 ? " AND " : "") + "  " + e.getKey() + " = " + e.getValue();
            }
            
        //    System.out.println("heuuu : "+where);
            //tableName dibiarkan seaddanya utk kebutuhan join dengan tabel lain
            String sql = "select tb."+value+", tb."+label+ " from "+tableName+ (where.length() > 0 ? " where " +  where : "")+" order by tb."+label;    
           // Query q = session.createQuery ("select tb."+value+", tb."+label+" from "+tableName+" as tb order by tb."+label);
             Query q = session.createQuery(sql);//+ (where.length() > 0 ? " where " +  where : ""));
            List<Object[]> ls = (List) q.list();
            
            for (Object[] obj : ls) {
                result.add(new SelectItem(obj[0], (String) obj[1]));
            }
          
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                   
                } catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        
        return result;
    }
    
    public Map<Integer, String> mapAll(String tableName, String value, String label) {
        Map<Integer, String> result = new LinkedHashMap<>();
        
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
                      
        try {
                        
            Query q = session.createQuery ("select tb."+value+", tb."+label+" from "+tableName+" as tb order by tb."+label);
            List<Object[]> ls = (List) q.list();
            
            for (Object[] obj : ls) {
                        
                result.put((Integer)obj[0], (String) obj[1]);
            }
              
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                   
                } catch (HibernateException e1) {}
                
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        //finally {}
        
        return result;
    }
    
    
    public Object getById(Class c, Integer id) {
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        Object ob = new Object();
        try {
                
            ob = (Object) session.get(c, id);
                
            tx.commit();

            } catch (RuntimeException e) {

                if (tx != null && tx.isActive()) {
                    try {tx.rollback(); } 
                    catch (HibernateException e1) {}
                }
                Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);       
            }

            finally {}
                
        return ob;
        
    }
    
    public Object getObjectSQL(String sql, Map<String, String> filters) {
        Object result = new Object();
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
              
        try {
            
            String where = "";
            if (filters!=null) {
                for (Map.Entry<String, String> e : filters.entrySet())
                    if (e.getValue()!=null) {
                        
                        if (e.getValue().toLowerCase().equals("notnull")) 
                            where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NOT NULL ";    
                        
                        else where += (where.length()>0 ? " AND " : "") + " lower(" + e.getKey() + ") = lower('" + e.getValue() + "')";
                    }
                        
                    else where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NULL ";    
            }
            
            Query q = session.createQuery (sql + (where.length() > 0 ? " where " +  where : "") );
            result = (Object) q.uniqueResult();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);       
        }
        
        finally { }
        
        return result;
    }
    
    public Object getObjectSQLdirect(String sql) {
        Object result = new Object();
        
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
              
        try {
            Query q = session.createSQLQuery(sql);
            result = (Object) q.uniqueResult();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally { }
        
        return result;
    }
    
    public BigDecimal countSQL(String sql) {
        BigDecimal result = BigDecimal.ZERO;
        
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
              
        try {
            Query q = session.createSQLQuery(sql);
            result = (BigDecimal) q.uniqueResult();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally { }
        
        return result;
    }
    
    public boolean saveData(Object data) {
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        boolean success = false;
        
        try {
            session.saveOrUpdate(data);
                         
            tx.commit();
            success = true;  
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                } catch (HibernateException e1) {
                    Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e1);
                }
            }
            success = false;  
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        
        return success;
    }
    
    //hanya save
    public boolean saveOnly(Object data) {
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        boolean success = false;
        
        try {
            session.save(data);
                         
            tx.commit();
            success = true;  
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                } catch (HibernateException e1) {
                    Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e1);
                }
            }
            success = false;  
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        
        return success;
    }
    
    //just update
    public boolean updateData(Object data) {
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        boolean success;
        
        try {
            session.update(data);
             
            tx.commit();
            success = true;  
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                } catch (HibernateException e1) {}
            }
            success = false;  
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        return success;
    }
    
    public boolean deleteData(Object data) {
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        boolean success = false;
        
        try {
            session.delete(data);
            tx.commit();
            success = true;  
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                } catch (HibernateException e1) {}
            }
            success = false;  
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {
        
        }
        
        return success;
    }
        
    public Numbering getNextTrxNumber(Integer formId, Integer branch, Integer produk, Integer defaultNumber) {
        this.session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        Connection con = session.connection();  // obtain JDBC connection from Session object
        Numbering numbering = new Numbering();
        
        CallableStatement callableStatement;
        //String spNextNumber = "{call getNextNumber(?,?,?,?,?)}";
        String spNextNumber = "{call numbering_modul.get_next_number(?,?,?,?,?,?)}";
        
        try {
            callableStatement = con.prepareCall(spNextNumber);
            callableStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(2, java.sql.Types.NUMERIC);
            callableStatement.setInt(3, formId);
            callableStatement.setInt(4, branch);
            callableStatement.setInt(5, produk);
            callableStatement.setInt(6, defaultNumber);
                
            // execute spNextNumber store procedure
            callableStatement.executeUpdate();
     
            //result= callableStatement.getString(1);
            //Integer nomer = callableStatement.getInt(2);
            //System.out.println(nomer);
            
            
            numbering.setTrxNum(callableStatement.getString(1));
            numbering.setTrxId(callableStatement.getInt(2));

            tx.commit();
        } catch (SQLException ex) {
                
            if (tx != null && tx.isActive()) {
                try { tx.rollback();
                } catch (HibernateException e1) {}
            }
                
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return numbering;
    }
    
    
    public List<Object> loadLazy(String domain, int start, int end, String sortField, SortOrder sortOrder, Map<String, String> filters) {
        
        session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        List<Object> result = null;
            
        try {
            /*
            String where = "";
            if (filters!=null) {
                for (Map.Entry<String, String> e : filters.entrySet()) {
                    if (e.getValue()!=null)
                        where += (where.length()>0 ? " AND " : "") + " lower(" + e.getKey() + ") like lower('%" + e.getValue() + "%')";
                }    
            }
            
            * 
            */
            String where = "";
            if (filters!=null) {
                for (Map.Entry<String, String> e : filters.entrySet())
                    if (e.getValue()!=null) {
                        switch (e.getValue().toLowerCase()) {
                            case "notnull":
                                where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NOT NULL ";
                                break;
                            case "null":
                                where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NULL ";
                                break;
                            
                            case "false":
                                where += (where.length()>0 ? " AND ((" : "") + e.getKey() + " = false) or ("+ e.getKey() + " is null))";
                                break;
                                
                            case "true":
                                where += (where.length()>0 ? " AND (" : "") + e.getKey() + " = true)";
                                break;
                                
                            default:
                                where += (where.length()>0 ? " AND " : "") + " lower(" + e.getKey() + ") like lower('%" + e.getValue() + "%')";
                                break;
                            
                                
                        }
                        
                    }
                    
            }
            
            String orderBy = "";
            if (sortField!=null && sortOrder==null)
                orderBy = " order by "+sortField+" "+sortOrder;
                        
            String hql = "from "+domain+ (where.length() > 0 ? " where " +  where : "")+orderBy;
            
            Query q = session.createQuery(hql).setMaxResults(end-start).setFirstResult(start);
            result = (List) q.list();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        return result;
    }
    
    public int countLazy(String domain, Map<String, String> filters) {
        
        session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        Long result = null;
            
        try {
            String where = "";
            if (filters!=null) {
                for (Map.Entry<String, String> e : filters.entrySet())
                    if (e.getValue()!=null) {
                        switch (e.getValue().toLowerCase()) {
                            case "notnull":
                                where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NOT NULL ";
                                break;
                            case "null":
                                where += (where.length()>0 ? " AND " : "") + e.getKey() + " IS NULL ";
                                break;
                            case "false":
                                where += (where.length()>0 ? " AND ((" : "") + e.getKey() + " = false) or ("+ e.getKey() + " is null))";
                                break;
                                
                            case "true":
                                where += (where.length()>0 ? " AND (" : "") + e.getKey() + " = true)";
                                break;    
                            default:
                                where += (where.length()>0 ? " AND " : "") + " lower(" + e.getKey() + ") like lower('%" + e.getValue() + "%')";
                                break;
                        }
                    }
                    
            }
                        
            String hql = "select count(*) from "+domain+ (where.length() > 0 ? " where " +  where : "");
            
            Query q = session.createQuery(hql);
            result = (Long) q.uniqueResult();
            tx.commit();
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {tx.rollback(); } 
                catch (HibernateException e1) {}
            }
            Logger.getLogger(CRUEDService.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        
        return result.intValue();
    }
    
}
